OpenEdge Data Management:<br />DataServer for Microsoft SQL Server Modifying tables to create PROGRESS

If you want to use the ROWID function with a data source, you must select an index to associate with this function. Adding a new integer column named PROGRESS_RECID and defining it as a single-component index is the preferred method. This method can only be applied to a table that does not currently have an identity column defined. If your table already has an identity column, you can support ROWID by selecting an existing index. See the "Defining the ROWID" section for instructions on selecting an existing index. The following procedure illustrates how to create the PROGRESS_RECID column to support ROWID.

To use the ROWID function with an MS SQL Server database, you must make changes to your data source table.

Perform these tasks:

Add a column of the integer data type named PROGRESS_RECID. The new column must be able to contain null, as shown:


alter table `table` add PROGRESS_RECID integer null

Add a column with identity characteristics named PROGRESS_RECID_IDENT_. The new column must have the integer data type, as shown:


alter table `table` add PROGRESS_RECID_IDENT_ integer identity

Create a trigger to maintain the PROGRESS_RECID column:

create trigger _TI_`table` ON table for insert as if ( select PROGRESS_RECID from inserted) is NULL begin update t set PROGRESS_RECID = i.IDENTITYCOL from table t JOIN inserted i ON t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_ select convert (int, @@identity) end

create trigger _TI_table ON table for insert as  
    if  ( select PROGRESS_RECID from inserted) is NULL  
      begin  
          update t set PROGRESS_RECID = i.IDENTITYCOL  
            from table t  JOIN inserted i ON  
             t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_  
             select convert (int, @@identity)  
      end

Create a non-unique index for the PROGRESS_RECID and a unique index for the PROGRESS_RECID_IDENT, respectively, according to the following syntax:


create index <`table`>`#_#`progress_recid on <`table`> (PROGRESS_RECID)


create unique index <`table`>#_#progress_recid_ident_on <`table`> `(PROGRESS_RECID_IDENT_)`

Change the nonunique indexes so that they include a PROGRESS_RECID column as the last component, as shown:


create index <`table>#_#<index>` on `table` (`column`, PROGRESS_RECID)

If you have already created your schema holder, delete and recreate it.

Modifying tables to create PROGRESS_RECID in support of the ROWID function